package database;

import java.util.Collections;
import java.util.HashMap;
import java.util.Map;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Time;

import java.sql.Connection;

import filesystem.FileSystem;

import objects.MessageCodes;
import objects.cluster.ClusterInfo;
import objects.cluster.ClusterStatusData;
import objects.cluster.MyJobData;
import objects.user.UserInfo;

import utils.Utils;



public class Database implements MessageCodes{
	public static Map<String, UserInfo> users = Collections
			.synchronizedMap(new HashMap<String, UserInfo>());
	
	public static byte registerUser(UserInfo ui) {

		try {
			Connection conn = ConnectionManager.ds.getConnection();
			String query = "INSERT INTO users VALUES (?,?,?,?)";
			PreparedStatement stmt = conn.prepareStatement(query);
			stmt.setInt(1, 0);
			stmt.setString(2, ui.username);
			stmt.setString(3, ui.password);
			stmt.setString(4, ui.email);
			int entriesModified = stmt.executeUpdate();
			if (entriesModified <= 0)
				return SQL_ERR;

			query = "SELECT id FROM users WHERE name = ?";
			stmt = conn.prepareStatement(query);
			stmt.setString(1, ui.username);
			ResultSet rs = stmt.executeQuery();
			if (!rs.first())
				return SQL_ERR;
			ui.db_id = rs.getInt("id");
			
			query = "INSERT INTO filesystem VALUES (?,?)";
			stmt = conn.prepareStatement(query);
			stmt.setInt(1, ui.db_id);
			stmt.setString(2, ui.fs.toJson());
			entriesModified = stmt.executeUpdate();
			if (entriesModified <= 0)
				return SQL_ERR;
			return OK;
		} catch (Exception e) {
			Utils.out(e.getMessage());
			return SQL_ERR;
		}
	}
	
	public static void updateFilesystem(UserInfo ui) {
		try {
			Connection conn = ConnectionManager.ds.getConnection();
			String query = "UPDATE filesystem SET data = ? WHERE id = ?";
			PreparedStatement stmt = conn.prepareStatement(query);
			stmt.setString(1, ui.fs.toJson());
			stmt.setInt(2, ui.db_id);
			stmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			Utils.out(e.getMessage());
		}
	}

	public static void getFilesystem(UserInfo ui) {
		try {
			Connection conn = ConnectionManager.ds.getConnection();
			String query = "SELECT data FROM filesystem WHERE id = ?";
			PreparedStatement stmt = conn.prepareStatement(query);
			stmt.setInt(1, ui.db_id);
			ResultSet rs = stmt.executeQuery();
			if (!rs.first()) {
				return;
			}
			String clob = rs.getString("data");
			ui.fs = FileSystem.fromJson(clob);
		} catch (Exception e) {
			e.printStackTrace();
			Utils.out(e.getMessage());
		}
	}

	public static byte registerCluster(UserInfo ui,
			ClusterInfo ci) {
		try {
			Connection conn = ConnectionManager.ds.getConnection();
			String query = "INSERT INTO clusters VALUES (?,?,?,?)";
			PreparedStatement stmt = conn.prepareStatement(query);
			stmt.setInt(1, 0);
			stmt.setString(2, ci.hostname);
			stmt.setString(3,ci.local_hostname);
			stmt.setInt(4,ci.local_hostname.contains("crystal") ? 4 : 8);
			int entriesModified = stmt.executeUpdate();
			if (entriesModified < 0)
				return SQL_ERR;

			query = "SELECT id,ppn FROM clusters WHERE host = ? and lhost = ?";
			stmt = conn.prepareStatement(query);
			stmt.setString(1, ci.hostname);
			stmt.setString(2, ci.local_hostname);
			ResultSet rs = stmt.executeQuery();
			if (!rs.first())
				return SQL_ERR;
			ci.db_id = rs.getInt("id");
			ci.ppn = rs.getInt("ppn");
			return addClusterToUser(ui, ci);
		} catch (Exception e) {
			Utils.out(e.getMessage());
			return SQL_ERR;
		}
	}

	public static byte addClusterToUser(UserInfo ui,
			ClusterInfo ci) {
		try {
			Connection conn = ConnectionManager.ds.getConnection();
			String query = "SELECT id FROM cu_ids WHERE user_id = ? and cluster_id = ?";
			PreparedStatement stmt = conn.prepareStatement(query);
			stmt.setInt(1,ui.db_id);
			stmt.setInt(2,ci.db_id);
			ResultSet rs = stmt.executeQuery();
			if(rs.first())
				return OK;
			query = "INSERT INTO cu_ids VALUES (?,?,?)";
			stmt = conn.prepareStatement(query);
			stmt.setInt(1, 0);
			stmt.setInt(2, ui.db_id);
			stmt.setInt(3, ci.db_id);
			int entriesModified = stmt.executeUpdate();
			if (entriesModified < 0)
				return SQL_ERR;
			return OK;
		} catch (Exception e) {
			Utils.out(e.getMessage());
			return SQL_ERR;
		}
	}

	public static ClusterInfo getClusterInfo(String hostname, String lhostname) {
		try {
			Connection conn = ConnectionManager.ds.getConnection();
			String query = "SELECT * FROM clusters WHERE host = ? and lhost = ?";
			PreparedStatement stmt = conn.prepareStatement(query);
			stmt.setString(1, hostname);
			stmt.setString(2, lhostname);
			ResultSet rs = stmt.executeQuery();
			if (!rs.first())
				return null;
			return new ClusterInfo(hostname,lhostname, rs.getInt("id"), rs.getInt("ppn"));
		} catch (Exception e) {
			Utils.out(e.getMessage());
			return null;
		}

	}
	
	public static int [] getCurrentClusterStatus(int clusterId)
	{
		try {
			Connection conn = ConnectionManager.ds.getConnection();
			String query = "SELECT * FROM clu_data WHERE id = ? order by id desc limit 1";
			PreparedStatement stmt = conn.prepareStatement(query);
			stmt.setInt(1, clusterId);
			ResultSet rs = stmt.executeQuery();
			if(rs.first()) {
				return new int[] { rs.getInt("freenodes"), rs.getInt("freeprocs"), rs.getInt("runjobs"), rs.getInt("qjobs") };
			}
		}catch(Exception e){ }
		return new int[]{};
	}

	public static byte getUserClusters(UserInfo ui) {
		try {
			Connection conn = ConnectionManager.ds.getConnection();
			String query = "SELECT cluster_id FROM cu_ids WHERE user_id = ?";
			PreparedStatement stmt = conn.prepareStatement(query);
			stmt.setInt(1, ui.db_id);
			ResultSet rs = stmt.executeQuery();
			if (rs.next()) {
				int cu_id = rs.getInt("cluster_id");
				query = "SELECT * FROM clusters WHERE id = ?";
				stmt = conn.prepareStatement(query);
				stmt.setInt(1, cu_id);
				rs = stmt.executeQuery();
				if (rs.next()) {
					int clusterId = rs.getInt("id");
					String hostname = rs.getString("host");
					String loc_hostname = rs.getString("lhost");
					int ppn = rs.getInt("ppn");
					ui.clusterMap.put(clusterId, new ClusterInfo(hostname,loc_hostname,clusterId,ppn));
				}
			}
			return OK;

		} catch (Exception e) {
			Utils.out(e.getMessage());
			return SQL_ERR;
		}
	}
	
	@SuppressWarnings("deprecation")
	public static void addClusterStatusInfo(int cluster,ClusterStatusData csi) throws Exception{
		Connection conn = ConnectionManager.ds.getConnection();
		String query = "INSERT INTO clu_data VALUES (?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement stmt = conn.prepareStatement(query);
		stmt.setInt(1,0);
		stmt.setInt(2,cluster);
		stmt.setInt(3,csi.freenodes);
		stmt.setInt(4,csi.freeprocs);
		stmt.setInt(5,csi.activejobs);
		stmt.setInt(6,csi.queuedjobs);
		stmt.setInt(7,Time.valueOf(csi.time).getHours());
		stmt.setInt(8,Date.valueOf(csi.date).getDay());
		stmt.setTime(9,Time.valueOf(csi.time));
		stmt.setDate(10,Date.valueOf(csi.date));
		
		int entriesModified = stmt.executeUpdate();
		if (entriesModified < 0)
			Utils.out("ERROR addding CSI");
	}
	
	public static void addMyJobsInfo(String cluster,MyJobData jsi) throws Exception{
		Connection conn = ConnectionManager.ds.getConnection();
		String query = "INSERT INTO myjobs VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement stmt = conn.prepareStatement(query);
		stmt.setInt(1,0);
		if(cluster.contains("bluecrystal"))
			stmt.setString(2,"bc1");
		else if(cluster.contains("bigblue"))
			stmt.setString(2,"bc2");
		else 
			stmt.setString(2,cluster);
		
		stmt.setInt(3,jsi.nodes);
		stmt.setInt(4,jsi.procs);
		stmt.setInt(5,jsi.nodes * jsi.procs);
		stmt.setInt(6,jsi.queuetime);
		stmt.setInt(7,jsi.walltime);
		stmt.setInt(8,jsi.freenodes);
		stmt.setInt(9,jsi.freeprocs);
		stmt.setInt(10,jsi.freenodes*jsi.freeprocs);
		stmt.setInt(11,jsi.activejobs);
		stmt.setInt(12,jsi.queuedjobs);
		stmt.setInt(13,jsi.time);
		stmt.setInt(14,jsi.date);
		stmt.setTimestamp(15, new java.sql.Timestamp(jsi.datetime));
		int entriesModified = stmt.executeUpdate();
		if (entriesModified < 0)
			Utils.out("ERROR addding JSI");
	}
	
	
	
	
	
	
	
	
	
	

	/*
	 * public static void execSQL(Connection conn, String query, UserInfo ui) {
		try {
			PreparedStatement stmt = conn.prepareStatement(query);
			ResultSet rs = stmt.executeQuery();
			ui.sendMessage("active nodes | active procs | num jobs | day | date | time");
			while (rs.next()) {
				ui.sendMessage(rs.getInt("upnodes") + "       "
						+ rs.getInt("upprocs") + "           " + rs.getInt("jobs") + "             "
						+ rs.getString("day") + "      " + rs.getString("date")
						+ "       " + rs.getString("time"));
			}
			ui.sendMessage("done");
		} catch (Exception e) {
			Utils.out(e.getMessage());
		}
	}*/
	/*public static byte setClusterUp(Connection conn, ClusterInfo ci,
			boolean isup) {
		try {
			String up = (isup ? "TRUE" : "FALSE");
			String query = "UPDATE clusters SET isup = '" + up
					+ "' WHERE id = ?";
			PreparedStatement stmt = conn.prepareStatement(query);
			stmt.setInt(1, ci.db_id);
			int entriesModified = stmt.executeUpdate();
			if (entriesModified < 0)
				return SQL_ERR;
			return OK;
		} catch (Exception e) {
			Utils.out(e.getMessage());
			return SQL_ERR;
		}
	}*/
/*
	public static byte updateClusterInfo(Connection conn, ClusterInfo ci) {
		try {
			String query = "UPDATE clusters SET rel = ? ,speed = ?  WHERE id = ?";
			PreparedStatement stmt = conn.prepareStatement(query);
			stmt.setDouble(1, ci.reliability);
			stmt.setDouble(2, ci.speed);
			//stmt.setDouble(3, ci.general);
			stmt.setInt(4, ci.db_id);
			int entriesModified = stmt.executeUpdate();
			if (entriesModified < 0)
				return SQL_ERR;
			return OK;
		} catch (Exception e) {
			Utils.out(e.getMessage());
			return SQL_ERR;
		}
	}*/

	

	/*
	public static void addJobStatusInfo(Connection conn,String cluster,JobStatusInfo jsi) throws Exception{
		String query = "INSERT INTO job_data VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement stmt = conn.prepareStatement(query);
		stmt.setInt(1,0);
		if(cluster.contains("bluecrystal"))
			stmt.setString(2,"bc1");
		else if(cluster.contains("bigblue"))
			stmt.setString(2,"bc2");
		else 
			stmt.setString(2,cluster);
		stmt.setString(3,jsi.owner);
		stmt.setString(4,jsi.jobname);
		stmt.setInt(5,jsi.nodes);
		stmt.setInt(6,jsi.procs);
		stmt.setLong(7,jsi.walltime);
		stmt.setInt(8, jsi.numjobs);
		stmt.setInt(9,jsi.queuetime);
		stmt.setTime(10,Time.valueOf(jsi.subtime));
		stmt.setDate(11,Date.valueOf(jsi.subdate));
		stmt.setTime(12,Time.valueOf(jsi.exectime));
		stmt.setDate(13,Date.valueOf(jsi.execdate));
		int entriesModified = stmt.executeUpdate();
		if (entriesModified < 0)
			Utils.out("ERROR addding JSI");
	}*/
	
	/*
	public static byte addClusterData(Connection conn, ClusterData cd,
			ClusterInfo ci) {
		try {
			String query = "INSERT INTO cluster_data VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
			PreparedStatement stmt = conn.prepareStatement(query);
			String[] t = cd.data;
			stmt.setInt(1, pi(t[0]));
			stmt.setInt(2, pi(t[1]));
			stmt.setInt(3, pi(t[2]));
			stmt.setInt(4, pi(t[3]));
			stmt.setInt(5, pi(t[4]));
			stmt.setInt(6, pi(t[5]));
			stmt.setDouble(7, pd(t[6]));
			stmt.setDouble(8, pd(t[7]));
			stmt.setDouble(9, pd(t[8]));
			stmt.setDouble(10, pd(t[9]));
			stmt.setString(11, t[10]);
			stmt.setDate(12, Date.valueOf(t[11]));
			stmt.setString(13, t[12]);
			int entriesModified = stmt.executeUpdate();
			if (entriesModified < 0)
				return SQL_ERR;
			/*ci.reliability = pd(t[8]);
			ci.speed = 1 / pd(t[7]);
			ci.general = (ci.reliability / 100) * ci.speed;
			return updateClusterInfo(conn, ci);
			return OK;
		} catch (Exception e) {
			Utils.out(e.getMessage());
			return SQL_ERR;
		}
	}*/
	/*public static void insertClusterStatusInfo(Connection conn,ClusterStatusInfo csi) throws Exception{
		String query = "INSERT INTO test_jobs_data VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement stmt = conn.prepareStatement(query);
		stmt.setInt(1,0);
		stmt.setString(2,csi.name);
		stmt.setInt(3,csi.nodes);
		stmt.setInt(4,csi.procs);
		stmt.setInt(5,csi.sleeptime);
		stmt.setDouble(6,csi.queuetime);
		stmt.setInt(7,csi.freenodes);
		stmt.setInt(8,csi.freeprocs);
		stmt.setInt(9,csi.activejobs);
		stmt.setInt(10,csi.queuedjobs);
		stmt.setString(11,csi.date);
		stmt.setInt(12,csi.day);
		int entriesModified = stmt.executeUpdate();
		if (entriesModified < 0)
			Utils.out("ERROR addding CSI");
	}*/

	public static int pi(String s) {
		return Integer.parseInt(s);
	}

	public static double pd(String s) {
		return Double.parseDouble(s);
	}
}
